﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

/// <summary>
/// singleton model - only 1 instance
/// </summary>
/// 

public static class DatabaseManager
{
    private static StringTokenizer st;
    private static string db_name;
    private static string db_file;
    private static string[] attributes;
    private static string[] values;
    private static string sqlConnectionString = "Server=.\\SQLExpress;AttachDbFilename=|DataDirectory|FelPay.mdf;Database=FelPay;Integrated Security=True;User Instance=True";
    private static SqlDataReader sql_reader = null;
    private static SqlConnection sql_connection = null;
    private static SqlCommand sql_command = null;

    public static string DbName
    {
        get { return db_name; }
        private set { db_name = value; }
    }

    public static string DbFile
    {
        get { return db_file; }
        private set { db_file = value; }
    }

    public static SqlCommand SqlCommand { get { return sql_command;} }

    public static SqlDataReader SqlReader { get { return sql_reader; } }
    
    public static bool connectLocal()
    { 
        DbName = "FelPay";
        DbFile = "FelPay.mdf";
        return connect("Server=.\\SQLExpress;AttachDbFilename=|DataDirectory|FelPay.mdf;Database=FelPay;Integrated Security=True;User Instance=True");
        
    }

    public static bool connectLocal(string db_name, string db_file, bool dbIsInProject)
    {
        DbName = db_name;
        DbFile = (dbIsInProject) ? "|DataDirectory|" + db_file : db_file;
        return connect("Server=.\\SQLExpress;AttachDbFilename=" + DbFile + ";Database=" + DbName + ";Integrated Security=True;User Instance=True");
    }

    public static bool connect(string where)
    {
        try
        {
            sqlConnectionString = where;
            makeConnection();
            setDbFileAndDbName(where);
            return true;
        }
        catch (Exception e) {
            e = e.InnerException;
            return false; 
        }
    }

    public static void makeConnection()
    {
        if (sql_connection == null || sql_connection.State != ConnectionState.Open)
        {
            sql_connection = new SqlConnection(sqlConnectionString);
            sql_connection.Open();
            sql_command = sql_connection.CreateCommand();
        }
    }

    public static void closeConnection()
    {
        if (sql_reader != null)
            sql_reader.Close();

        if (sql_command != null)
            sql_command.Dispose();

        if (sql_connection != null)
            sql_connection.Close();
    }

    private static void setDbFileAndDbName(string where)
    {
        StringTokenizer st = new StringTokenizer(where, new char[] { ';' });
        foreach (string token in st)
        {
            if (token.StartsWith("AttachDbFilename"))
            {
                // try to parse value for DbFile
                DbFile = token.Substring("AttachDbFilename=".Length);
                continue;
            }
            if (token.StartsWith("Database"))
            {
                // try to parse value for DbName
                DbName = token.Substring("Database=".Length);
                continue;
            }
        }
    }

    private static Dictionary<int, string[]> readResults()
    {
        try
        {
            // execute reader
            sql_reader = sql_command.ExecuteReader();

            Dictionary<int, string[]> results = null;
            string[] row = null;

            // if we have some data to read
            while (sql_reader.Read())
            {
                // setup variables
                if (row == null)
                {
                    row = new string[sql_reader.FieldCount];
                    results = new Dictionary<int, string[]>();
                }

                // reading data
                for (int i = 0; i < row.Length; i++)
                    row[i] = sql_reader[i].ToString();

                // adding to Dictionary
                results.Add(results.Count, (string[])row.Clone());
            }

            return results;
        }
        catch (SqlException e)
        {
            Console.WriteLine(e.Message);
            return null;
        }
        finally
        {
            sql_reader.Close();
        }
    }

    #region simple
    
    public static Person getPerson(string email)
    {
        return (Person)simpleGet(Configuration.Tables.person, email);

    }

    public static bool savePerson(ref Person p)
    {
        object o = (object)p;
        return simpleSave(Configuration.Tables.person ,ref o);
    }

    public static bool editPerson(string id, ref Person editedPerson)
    {
        object o = (object)editedPerson;
        return simpleEdit(Configuration.Tables.person, id, ref o);
    }
    public static bool editPerson(Person editedPerson)
    {
        return editPerson(editedPerson.Email, ref editedPerson);
    }
    public static bool removePerson(string id)
    {
        return simpleRemove(Configuration.Tables.person, id);
    }   

    public static Account getAccount(string id)
    {
        return (Account)simpleGet(Configuration.Tables.account, id);
    }

    public static bool saveAccount(ref Account a)
    {
        object o = (object)a;
        return simpleSave(Configuration.Tables.account, ref o);
    }

    public static bool editAccount(string id, ref Account editedAccount)
    {
        object o = (object)editedAccount;
        return simpleEdit(Configuration.Tables.account, id, ref o);
    }

    public static bool removeAccount(string id)
    {
        return simpleRemove(Configuration.Tables.account, id);
    }

    public static Payment getPayment(string id)
    {
        return (Payment)simpleGet(Configuration.Tables.payment, id);
    }

    public static bool savePayment(ref Payment p)
    {
        object o = (object) p;
        return simpleSave(Configuration.Tables.payment, ref o);
    }

    public static bool editPayment(string id, ref Payment editedPayment)
    {
        object o = (object)editedPayment;
        return simpleEdit(Configuration.Tables.payment, id, ref o);
        
    }

    public static bool removePayment(string id)
    {
        return simpleRemove(Configuration.Tables.payment, id);
    }

    #endregion simple

    #region specific

   /* public static HashSet<Payment> getPaymentHistoryByPersonId(string email)
    {
        HashSet<StatementCollection> h = new HashSet<StatementCollection>();
        HashSet<Payment> p = new HashSet<Payment>();
        StatementCollection s = new StatementCollection("from_account", "=", email);
        h.Add(s);

        h.Add(new StatementCollection("status", "=", "paid"));

        Dictionary<int, string[]> d = complexGet(Configuration.Tables.paymentsbyperson, h, true);

        if (d == null)
            return null;

        foreach(int index in d.Keys)
            p.Add(Payment.newInstance(d[index]));

        return p;
    }*/

    public static HashSet<Payment> getPaymentHistoryByPersonId(string email)
    {
        HashSet<Payment> p = new HashSet<Payment>();
        try
        {
            Person person = getPerson(email);
            // creates new connection
            makeConnection();

            sql_command.CommandText = "dbo.getPaymentHistory";
            sql_command.CommandType = CommandType.StoredProcedure;
            
            sql_command.Parameters.AddWithValue("@accountID", person.Account);

            Dictionary<int, string[]> d = readResults();

            if (d == null) return p;
            foreach (int index in d.Keys)
                p.Add(Payment.newInstance(d[index]));

            
        }
        catch (SqlException e)
        {
            Console.WriteLine(e.Message);
        }
        finally
        {
            closeConnection();
        }
        return p;
    }
    // TK
    public static int getCountPaymentRequest(string from_account)
    {
        HashSet<Payment> payments  = getPaymentReguestByPersonId(from_account);
        if (payments == null) return 0;
        return payments.Count;
    }

    public static HashSet<Payment> getPaymentReguestByPersonId(string from_account)
    {
        HashSet<StatementCollection> h = new HashSet<StatementCollection>();
        HashSet<Payment> p = new HashSet<Payment>();
        StatementCollection s = new StatementCollection("from_account", "=", getPerson(from_account).Account);
        h.Add(s);
        h.Add(new StatementCollection("type", "=", "eshop"));
        h.Add(new StatementCollection("status", "=", "created"));

        Dictionary<int, string[]> d = complexGet(Configuration.Tables.payment, h, true);

        if (d == null)
            return null;

        foreach (int index in d.Keys)
            p.Add(Payment.newInstance(d[index]));

        return p;
    }

    public static Person getPersonByAccont(string Accont)
    {
        HashSet<StatementCollection> h = new HashSet<StatementCollection>();
        StatementCollection s = new StatementCollection("account", "=", Accont);
        h.Add(s);

        Dictionary<int, string[]> d = complexGet(Configuration.Tables.person, h, true);

        if ((d == null) || (d.Count != 1))
            return null;

        return Person.newInstance(d[0]);
    }
    public static Payment getPaymentByGUID(Guid guid)
    {
        string sGuid = guid.ToString();
        HashSet<StatementCollection> h = new HashSet<StatementCollection>();
        StatementCollection s = new StatementCollection("guid", "=", sGuid);
        h.Add(s);

        Dictionary<int, string[]> d = complexGet(Configuration.Tables.payment, h, true);

        if (d == null)
            return null;
        if (d.Count != 1) return null;
        return Payment.newInstance(d[0]);
    }
    public static HashSet<Payment> getPaymentHistoryByGUID(Guid guid)
    {
        string sGuid = guid.ToString();
        HashSet<StatementCollection> h = new HashSet<StatementCollection>();
        HashSet<Payment> p = new HashSet<Payment>();
        StatementCollection s = new StatementCollection("guid", "=", sGuid);
        h.Add(s);

        Dictionary<int, string[]> d = complexGet(Configuration.Tables.paymentsbyperson, h, true);

        if (d == null)
            return null;

        foreach (int index in d.Keys)
            p.Add(Payment.newInstance(d[index]));

        return p;

    }

    public static Payment saveOnlinePayment(string senderId, string recipientId, int ammount, bool isAnonymous, string due_date, string comment)
    {
        Person sender = getPerson(senderId);
        Person recipient = new Person();
        
        if (recipientId != null)
            recipient = getPerson(recipientId);

        string guid = Guid.NewGuid().ToString(); // TK> potřebujeme tady GUID, proč?
        Payment p = new Payment("0", guid, sender.Account, null, due_date, null, 
                                Configuration.PaymentStatus.created.ToString("g"), 
                                Configuration.PaymentType.online.ToString("g"), 
                                Convert.ToString(ammount), recipient.Account, comment);

        savePayment(ref p);
        
        return p;
    }

    public static Payment saveCheck(string senderId, string recipientId, int ammount, string due_date)
    {
        Person sender = getPerson(senderId);
        Person recipient = new Person();

        if (recipientId != null)
            recipient = getPerson(recipientId);

        string guid = Guid.NewGuid().ToString();
        Payment p = new Payment("0", guid, sender.Account, null, due_date, null,
                                Configuration.PaymentStatus.created.ToString("g"),
                                Configuration.PaymentType.check.ToString("g"),
                                Convert.ToString(ammount), recipient.Account, null);

        savePayment(ref p);

        return p;
    }

    public static Payment savePaymentByRequest(string senderId, string recipientId, int amount, string URL_Response, string comment)
    {
        Person sender = getPerson(senderId);
        Person recipient = new Person();

        if (recipientId != null)
            recipient = getPerson(recipientId);

        string guid = URL_Response; // když sme zapomenli na to url tak ho musime nekde vrazit :)
        
        DateTime due_date = DateTime.Now;
        due_date.AddMonths(Configuration.GeneralDueDateTime);

        Payment p = new Payment("0", guid, sender.Account, DateTime.Now.ToString(), due_date.ToString(), DateTime.Now.ToString(),
                                Configuration.PaymentStatus.created.ToString("g"),
                                Configuration.PaymentType.eshop.ToString("g"), // tady by melo byt Eshop a ne check
                                amount.ToString(), recipient.Account,comment);

        savePayment(ref p);

        return p;

    }

    public static Account getAccountBePersonId(string id)
    {
        Person p = getPerson(id);
        Account a = getAccount(p.Account);
        
        return a;
    }
    #endregion specific

    #region genereal

    private static object simpleGet(Configuration.Tables where, string id)
    {
        try
        {
            HashSet<StatementCollection> h = new HashSet<StatementCollection>();
            Dictionary<int, string[]> d;
            StatementCollection s = null;

            switch (where)
            {
                case Configuration.Tables.person: s = new StatementCollection("email", "=", id);
                    break;
                case Configuration.Tables.account: s = new StatementCollection("id", "=", id);
                    break;
                case Configuration.Tables.payment: s = new StatementCollection("id", "=", id);
                    break;
            }

            // add statement to hashset
            h.Add(s);

            
            d = complexGet(where, h, true);

            if (d == null)
                return null;

            switch (where)
            {
                case Configuration.Tables.person: return Person.newInstance(d[0]);
                case Configuration.Tables.account: return Account.newInstance(d[0]);
                case Configuration.Tables.payment: return Payment.newInstance(d[0]);
                default: return null;
            }
        }
        catch (SqlException e)
        {
            Console.WriteLine(e.Message);
            return null;
        }
        finally
        {
            closeConnection();
        }
    }

    private static bool simpleSave(Configuration.Tables where, ref object o)
    {
        try
        {
            // creates new connection
            makeConnection();


            // set necessary
            switch (where)
            {
                case Configuration.Tables.person:   sql_command.CommandText = "dbo.SavePerson";

                                                    // read attributes and values for Person
                                                    attributes = Configuration.EnumtoArray<Configuration.PersonAttributes>();
                                                    values = ((Person)o).toArray();
                                                    break;
                case Configuration.Tables.account:  sql_command.CommandText = "dbo.SaveAccount";

                                                    // read attributes and values for Person
                                                    attributes = Configuration.EnumtoArray<Configuration.AccountAttributes>();
                                                    values = ((Account)o).toArray();
                                                    break;
                case Configuration.Tables.payment:  sql_command.CommandText = "dbo.SavePayment";

                                                    // read attributes and values for Person
                                                    attributes = Configuration.EnumtoArray<Configuration.PaymentAttributes>();
                                                    values = ((Payment)o).toArray();
                                                    break;
            }

            sql_command.CommandType = CommandType.StoredProcedure;

            // insert attributes and values into stored procedure
            for (int i = 0; i < attributes.Length; i++)
                sql_command.Parameters.AddWithValue("@" + attributes[i], values[i]);

            
            // set right id to object
            int id;

            // return statement
            switch (where)
            {
                case Configuration.Tables.person:   bool ok = (sql_command.ExecuteNonQuery() > 0);
                                                    return ok;

                case Configuration.Tables.account:  id = Decimal.ToInt32((decimal)sql_command.ExecuteScalar());
                                                    ((Account)o).Id = Convert.ToString(id);
                                                    return id != null;

                case Configuration.Tables.payment:  id = Decimal.ToInt32((decimal)sql_command.ExecuteScalar());
                                                    ((Payment)o).Id = Convert.ToString(id);
                                                    return id != null;
                default: return false;
            }
        }
        catch (SqlException e)
        {
            Console.WriteLine(e.Message);
            return false;
        }
        finally
        {
            closeConnection();
        }
    }

    private static bool simpleEdit(Configuration.Tables where, string id, ref Object o)
    {
        try
        {
            HashSet<StatementCollection> val = new HashSet<StatementCollection>();
            HashSet<StatementCollection> cond = new HashSet<StatementCollection>();

            StatementCollection s = null;
            string idColumn = null;
            string[] values = null;
            string[] columns = null;

            // set necessary
            switch (where)
            {
                case Configuration.Tables.person: values = ((Person)o).toArray();
                    columns = Configuration.EnumtoArray<Configuration.PersonAttributes>();
                    idColumn = Configuration.PersonAttributes.email.ToString("g");
                    break;
                case Configuration.Tables.account: values = ((Account)o).toArray();
                    columns = Configuration.EnumtoArray<Configuration.AccountAttributes>();
                    idColumn = Configuration.AccountAttributes.id.ToString("g");
                    break;
                case Configuration.Tables.payment: values = ((Payment)o).toArray();
                    columns = Configuration.EnumtoArray<Configuration.PaymentAttributes>();
                    idColumn = Configuration.PaymentAttributes.id.ToString("g");
                    break;
            }

            int i = 0;


            // collecting values
            foreach (string column in columns)
            {
                s = new StatementCollection(column, "=", values[i++]);
                if (column != "id") val.Add(s);
            }

            // set condition
            cond.Add(new StatementCollection(idColumn, "=", id));

            bool ok = complexEdit(where, val, cond, true);
            return ok;
        }
        catch (SqlException e)
        {
            Console.WriteLine(e.Message);
            return false;
        }
        finally
        {
            closeConnection();
        }
    }

    private static bool simpleRemove(Configuration.Tables where, string id)
    {
        try
        {
            HashSet<StatementCollection> cond = new HashSet<StatementCollection>();
            string column = null;

            // set necessary
            switch (where)
            {
                case Configuration.Tables.person: column = Configuration.PersonAttributes.email.ToString("g");
                    break;
                case Configuration.Tables.account: column = Configuration.AccountAttributes.id.ToString("g");
                    break;
                case Configuration.Tables.payment: column = Configuration.PaymentAttributes.id.ToString("g");
                    break;
            }

            // add condition to HashSet collection
            cond.Add(new StatementCollection(column, "=", id));

            bool ok = complexRemove(where, cond, true);
            return ok;
        }
        catch (SqlException e)
        {
            Console.WriteLine(e.Message);
            return false;
        }
        finally
        {
            closeConnection();
        }
    }

    private static Dictionary<int,string[]> complexGet(Configuration.Tables where, HashSet<StatementCollection> condition, bool AndJoins )
    {
        try
        {
            // creates new connection
            makeConnection();

            // set sql command
            sql_command.CommandText = "SELECT * FROM " + where.ToString("g");

            // counter for values.Keys
            int i = 1;

            // if null or zero value -> statement: SELECT * FROM "where"
            if (condition != null && condition.Count > 0)
            {
                sql_command.CommandText += " WHERE ";
                foreach (StatementCollection s in condition)
                {
                    if (i == condition.Count) // its last statement collection
                        sql_command.CommandText += s.Column + " " + s.Comparator + " '" + s.Value + "'";
                    else
                        sql_command.CommandText += s.Column + " " + s.Comparator + " '" + s.Value + "' " + ((AndJoins) ? " AND " : " OR ");
                    i++;
                }
            }

            // temporary save results
            Dictionary<int, string[]> d = readResults();

            return d;

        }
        catch (SqlException e)
        {
            Console.WriteLine(e.Message);
            return null;
        }
        finally
        {
            closeConnection();
        }
        
    }

    private static bool complexEdit(Configuration.Tables where, HashSet<StatementCollection> what, HashSet<StatementCollection> condition, bool AndJoins)
    {
        try
        {
            // creates new connection
            makeConnection();

            // counter
            int i = 1;

            sql_command.CommandText = "UPDATE " + where.ToString("g") + " SET ";

            // set new values
            foreach (StatementCollection s in what)
            {
                if (i == what.Count) // its last
                    sql_command.CommandText += s.Column + " " + s.Comparator + " '" + s.Value + "'";
                else sql_command.CommandText += s.Column + " " + s.Comparator + " '" + s.Value + "', ";
                i++;
            }
            i = 1;

            sql_command.CommandText += " WHERE ";

            // set conditions
            foreach (StatementCollection s in condition)
            {
                if (i == condition.Count) // its last
                    sql_command.CommandText += s.Column + " " + s.Comparator + " '" + s.Value + "'";
                else sql_command.CommandText += s.Column + " " + s.Comparator + " '" + s.Value + "' " + ((AndJoins) ? "AND" : "OR");
                i++;
            }

            bool ok = sql_command.ExecuteNonQuery() > 0;
            return ok;
        }
        catch (SqlException e)
        {
            Console.WriteLine(e.Message);
            return false;
        }
        finally 
        {
            closeConnection();
        }
    }

    private static bool complexRemove(Configuration.Tables where, HashSet<StatementCollection> condition, bool AndJoins)
    {
        try
        {
            // creates new connection
            makeConnection();

            sql_command.CommandText = "DELETE FROM " + where.ToString("g") + " WHERE ";

            // counter
            int i = 1;


            // set conditions
            foreach (StatementCollection s in condition)
            {
                if (i == condition.Count) // its last
                    sql_command.CommandText += s.Column + " " + s.Comparator + " '" + s.Value + "'";
                else
                    sql_command.CommandText += s.Column + " " + s.Comparator + " '" + s.Value + "' " + ((AndJoins) ? "AND " : "OR");
            }

            bool ok = sql_command.ExecuteNonQuery() > 0;
            return ok;
        }
        catch (SqlException e)
        {
            Console.WriteLine(e.Message);
            return false;
        }
        finally
        {
            closeConnection();
        }
    }

    #endregion general

    private class StatementCollection
    {
        private string column;
        private string comparator;
        private string value;

        public StatementCollection(string column, string comparator, string value)
        {
            this.column = column;
            this.comparator = comparator;
            this.value = value;
        }

        public string Column { get { return this.column; } set { this.column = value; } }
        public string Comparator { get { return this.comparator; } set { this.comparator = value; } }
        public string Value { get { return this.value; } set { this.value = value; } }
    }
}



